package com.css.zfzx.sjcj.modules.qhdrilloverview.repository.impl;

import com.css.zfzx.sjcj.modules.qhdrilloverview.repository.QhDrillOverviewNativeRepository;
import com.css.zfzx.sjcj.modules.qhdrilloverview.repository.entity.QhDrillOverviewEntity;
import com.css.zfzx.sjcj.modules.qhdrilloverview.viewobjects.QhDrillOverviewQueryParams;
import com.css.bpm.platform.utils.PlatformObjectUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
 * @author aa
 * @date 2020-11-11
 */
@Repository
public class QhDrillOverviewNativeRepositoryImpl implements QhDrillOverviewNativeRepository {
    @PersistenceContext
    private EntityManager em;

    private static final Logger log = LoggerFactory.getLogger(QhDrillOverviewNativeRepositoryImpl.class);

    @Override
    public Page<QhDrillOverviewEntity> queryQhDrillOverviews(QhDrillOverviewQueryParams queryParams, int curPage, int pageSize) {
        if(PlatformObjectUtils.isEmpty(queryParams.getIsValid())) {
            queryParams.setIsValid("1");
        }
        StringBuilder sql = new StringBuilder("select * from qh_drill_overview ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getReportName())) {
            whereSql.append(" and report_name like :reportName");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            whereSql.append(" and project_name like :projectName");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getConstructionInstitution())) {
            whereSql.append(" and construction_institution like :constructionInstitution");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            whereSql.append(" and province = :province");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            whereSql.append(" and city = :city");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            whereSql.append(" and area = :area");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTown())) {
            whereSql.append(" and town like :town");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillCode())) {
            whereSql.append(" and drill_code like :drillCode");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getReviewStatus())) {
            whereSql.append(" and review_status = :reviewStatus");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSiteClass())) {
            whereSql.append(" and site_class = :siteClass");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthBegin())) {
            whereSql.append(" and drill_depth >= :drillDepthBegin");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthEnd())) {
            whereSql.append(" and drill_depth <= :drillDepthEnd");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingBegin())) {
            whereSql.append(" and construction_complete_date >= :poreFormingBegin");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingEnd())) {
            whereSql.append(" and construction_complete_date <= :poreFormingEnd");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLongitude())) {
            whereSql.append(" and longitude = :longitude");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLatitude())) {
            whereSql.append(" and latitude = :latitude");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionResult())) {
            whereSql.append(" and qualityinspection_result = :qualityinspectionResult");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionStatus())) {
            whereSql.append(" and qualityinspection_status = :qualityinspectionStatus");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getIsValid())) {
            whereSql.append(" and is_valid = :isValid");
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), QhDrillOverviewEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from qh_drill_overview ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        if(!PlatformObjectUtils.isEmpty(queryParams.getReportName())) {
            query.setParameter("reportName", "%" + queryParams.getReportName() + "%");
            countQuery.setParameter("reportName", "%" + queryParams.getReportName() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            query.setParameter("projectName", "%" + queryParams.getProjectName() + "%");
            countQuery.setParameter("projectName", "%" + queryParams.getProjectName() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getConstructionInstitution())) {
            query.setParameter("constructionInstitution", "%" + queryParams.getConstructionInstitution() + "%");
            countQuery.setParameter("constructionInstitution", "%" + queryParams.getConstructionInstitution() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            query.setParameter("province", queryParams.getProvince());
            countQuery.setParameter("province", queryParams.getProvince());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            query.setParameter("city", queryParams.getCity());
            countQuery.setParameter("city", queryParams.getCity());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            query.setParameter("area", queryParams.getArea());
            countQuery.setParameter("area", queryParams.getArea());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTown())) {
            query.setParameter("town", "%" + queryParams.getTown() + "%");
            countQuery.setParameter("town", "%" + queryParams.getTown() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillCode())) {
            query.setParameter("drillCode", "%" + queryParams.getDrillCode() + "%");
            countQuery.setParameter("drillCode", "%" + queryParams.getDrillCode() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getReviewStatus())) {
            query.setParameter("reviewStatus", queryParams.getReviewStatus());
            countQuery.setParameter("reviewStatus", queryParams.getReviewStatus());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSiteClass())) {
            query.setParameter("siteClass", queryParams.getSiteClass());
            countQuery.setParameter("siteClass", queryParams.getSiteClass());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthBegin())) {
            float depth = Float.parseFloat(queryParams.getDrillDepthBegin());
            query.setParameter("drillDepthBegin", depth);
            countQuery.setParameter("drillDepthBegin", depth);
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthEnd())) {
            float depth = Float.parseFloat(queryParams.getDrillDepthEnd());
            query.setParameter("drillDepthEnd", depth);
            countQuery.setParameter("drillDepthEnd", depth);
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingBegin())) {
            query.setParameter("poreFormingBegin", queryParams.getPoreFormingBegin());
            countQuery.setParameter("poreFormingBegin", queryParams.getPoreFormingBegin());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingEnd())) {
            query.setParameter("poreFormingEnd", queryParams.getPoreFormingEnd());
            countQuery.setParameter("poreFormingEnd", queryParams.getPoreFormingEnd());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLongitude())) {
            query.setParameter("longitude", queryParams.getLongitude());
            countQuery.setParameter("longitude", queryParams.getLongitude());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLatitude())) {
            query.setParameter("latitude", queryParams.getLatitude());
            countQuery.setParameter("latitude", queryParams.getLatitude());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionResult())) {
            query.setParameter("qualityinspectionResult", queryParams.getQualityinspectionResult());
            countQuery.setParameter("qualityinspectionResult", queryParams.getQualityinspectionResult());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionStatus())) {
            query.setParameter("qualityinspectionStatus", queryParams.getQualityinspectionStatus());
            countQuery.setParameter("qualityinspectionStatus", queryParams.getQualityinspectionStatus());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getIsValid())) {
            query.setParameter("isValid", queryParams.getIsValid());
            countQuery.setParameter("isValid", queryParams.getIsValid());
        }
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<QhDrillOverviewEntity> list = query.getResultList();
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Page<QhDrillOverviewEntity> qhDrillOverviewPage = new PageImpl<>(list, pageable, count.longValue());
        return qhDrillOverviewPage;
    }

    @Override
    public List<QhDrillOverviewEntity> listQhDrillOverviews(QhDrillOverviewQueryParams queryParams) {
        if(PlatformObjectUtils.isEmpty(queryParams.getIsValid())) {
            queryParams.setIsValid("1");
        }
        StringBuilder sql = new StringBuilder("select * from qh_drill_overview ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getReportName())) {
            whereSql.append(" and report_name like :reportName");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            whereSql.append(" and project_name like :projectName");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getConstructionInstitution())) {
            whereSql.append(" and construction_institution like :constructionInstitution");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            whereSql.append(" and province = :province");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            whereSql.append(" and city = :city");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            whereSql.append(" and area = :area");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTown())) {
            whereSql.append(" and town like :town");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillCode())) {
            whereSql.append(" and drill_code like :drillCode");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getReviewStatus())) {
            whereSql.append(" and review_status = :reviewStatus");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSiteClass())) {
            whereSql.append(" and site_class = :siteClass");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthBegin())) {
            whereSql.append(" and drill_depth >= :drillDepthBegin");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthEnd())) {
            whereSql.append(" and drill_depth <= :drillDepthEnd");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingBegin())) {
            whereSql.append(" and construction_complete_date >= :poreFormingBegin");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthEnd())) {
            whereSql.append(" and construction_complete_date <= :poreFormingEnd");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingBegin())) {
            whereSql.append(" and construction_complete_date >= :poreFormingBegin");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLongitude())) {
            whereSql.append(" and longitude = :longitude");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLatitude())) {
            whereSql.append(" and latitude = :latitude");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionResult())) {
            whereSql.append(" and qualityinspection_result = :qualityinspectionResult");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionStatus())) {
            whereSql.append(" and qualityinspection_status = :qualityinspectionStatus");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getIsValid())) {
            whereSql.append(" and is_valid = :isValid");
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), QhDrillOverviewEntity.class);
        if(!PlatformObjectUtils.isEmpty(queryParams.getReportName())) {
            query.setParameter("reportName", "%" + queryParams.getReportName() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            query.setParameter("projectName", "%" + queryParams.getProjectName() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getConstructionInstitution())) {
            query.setParameter("constructionInstitution", "%" + queryParams.getConstructionInstitution() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            query.setParameter("province", queryParams.getProvince());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            query.setParameter("city", queryParams.getCity());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            query.setParameter("area", queryParams.getArea());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTown())) {
            query.setParameter("town", "%" + queryParams.getTown() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillCode())) {
            query.setParameter("drillCode", "%" + queryParams.getDrillCode() + "%");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getReviewStatus())) {
            query.setParameter("reviewStatus", queryParams.getReviewStatus());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSiteClass())) {
            query.setParameter("siteClass", queryParams.getSiteClass());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthBegin())) {
            float depth = Float.parseFloat(queryParams.getDrillDepthBegin());
            query.setParameter("drillDepthBegin", depth);
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getDrillDepthEnd())) {
            float depth = Float.parseFloat(queryParams.getDrillDepthEnd());
            query.setParameter("drillDepthEnd", depth);
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingBegin())) {
            query.setParameter("poreFormingBegin", queryParams.getPoreFormingBegin());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getPoreFormingEnd())) {
            query.setParameter("poreFormingEnd", queryParams.getPoreFormingEnd());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLongitude())) {
            query.setParameter("longitude", queryParams.getLongitude());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getLatitude())) {
            query.setParameter("latitude", queryParams.getLatitude());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionResult())) {
            query.setParameter("qualityinspectionResult", queryParams.getQualityinspectionResult());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getQualityinspectionStatus())) {
            query.setParameter("qualityinspectionStatus", queryParams.getQualityinspectionStatus());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getIsValid())) {
            query.setParameter("isValid", queryParams.getIsValid());
        }
        List<QhDrillOverviewEntity> list = query.getResultList();
        return list;
    }
}
